﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SQLite;
using System.Data;
using System.Windows.Forms;
using System.IO;//彭添加


namespace LibraryManagementSystem
{
    class Sqlcmd
    {
        static SQLiteConnection con = new SQLiteConnection("data source=E:\\.net\\Library\\libry.db");

        
        public static DataTable Select_book(string search_info, int searchtype)
        {//查看图书信息
            try
            {
                con.Open();
                string query = "";
                if (searchtype == 0)//输出全部数据
                    query = "SELECT bookid,locations,bookname,writer,press,bookinfo,pub_time,type,quantity FROM bookview";
                else if (searchtype == 1)//根据书名搜索
                    query = "SELECT bookid,locations,bookname,writer,press,bookinfo,pub_time,type,quantity FROM bookview WHERE bookname LIKE '%" + search_info + "%'";
                else if (searchtype == 2)//根据作者搜索
                    query = "SELECT bookid,locations,bookname,writer,press,bookinfo,pub_time,type,quantity FROM bookview WHERE writer LIKE '%" + search_info + "%'";
                else if (searchtype == 3)//根据图书类别搜索
                    query = "SELECT bookid,locations,bookname,writer,press,bookinfo,pub_time,type,quantity FROM bookview WHERE type LIKE '%" + search_info + "%'";
                //SQLiteCommand cmd = new SQLiteCommand(query, con);//使用sql查找指令
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, con);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch { MessageBox.Show("数据为空！"); con.Close(); return null; }
        }

        //查看书本详情
        public static DataTable Get_details(int bookid)
        {
            try
            {
                con.Open();
                string query = "SELECT area,location,borrowed FROM bookdetails WHERE bookid=" + bookid;
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, con);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch { con.Close(); return null; }
        }

        //删除图书
        public static void Delete(bool type, string id)
        {
            try
            {
                con.Open();
                string query = "";
                if (!type)
                    query = "DELETE FROM book_located WHERE id='" + id + "'";//sql语句
                else query = "DELETE FROM book WHERE bookid=" + id;
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    MessageBox.Show("删除成功！");
                }
                else
                {
                    MessageBox.Show("删除失败！");
                }
                con.Close();
            }
            catch { MessageBox.Show("删除失败！"); con.Close(); }
        }
        //修改
        public static void Change(string table, int bookid, string changetext)
        {
            try
            {
                con.Open();
                string query = "UPDATE " + table + " SET " + changetext + " WHERE " + "bookid=" + bookid;//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    MessageBox.Show("修改成功！");
                }
                else
                {
                    MessageBox.Show("修改失败！");
                }
                con.Close();
            }
            catch { MessageBox.Show("修改失败！"); con.Close(); }
        }

        //添加书籍
        public static void Add_book(string bookname, string writer, string press, string bookinfo, string pub_time, string type, int quantity)
        {
            try
            {
                con.Open();
                string query = "INSERT INTO book(bookname,writer,press,bookinfo,pub_time,type,quantity) VALUES('" + bookname + "','" + writer + "','" + press + "','" + bookinfo + "','" + pub_time + "','" + type + "'," + quantity + ")";//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); con.Close(); }
        }

        //获取书籍bookid
        public static int Get_bookid(string bookname, string writer)
        {
            con.Open();
            string getbookid = "SELECT bookid FROM book WHERE bookname='" + bookname + "' AND writer='" + writer + "'";
            SQLiteCommand cmd = new SQLiteCommand(getbookid, con);
            SQLiteDataReader reader = cmd.ExecuteReader();
            reader.Read();
            int bookid = reader.GetInt32(reader.GetOrdinal("bookid"));
            con.Close();
            return bookid;
        }
        //添加书籍位置
        public static void Add_detail(string bookname, char area, string location, bool borrowed, int bookid)
        {
            try
            {
                con.Open();
                string id = area + "-" + location;
                string query = "INSERT INTO book_located(id,bookid,bookname,area,location,borrowed) VALUES('" + id + "'," + bookid + ",'" + bookname + "','" + area + "','" + location + "'," + borrowed + ")";//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); con.Close(); }
        }
    }
}
